try:
    from sqlalchemy import create_engine
    from sqlalchemy import Table, Column, Integer, String, DateTime, MetaData, ForeignKey
    from sqlalchemy.orm import sessionmaker,mapper,relation,backref
except ImportError:
    raise

from utility_functions import *
from urlparse import urlparse
from string import join

import datetime
import sys
import os.path
import socket
import httpagentparser

try:
    import GeoIP
    GEO_IP_INSTALLED = True
    GeoByIp = GeoIP.new(GeoIP.GEOIP_MEMORY_CACHE)
except ImportError:
    GEO_IP_INSTALLED = False

GEO_IP_CITY_INSTALLED = False
GEO_IP_CITY_DATABASE = "/usr/local/share/GeoIP/GeoIPCity.dat"

if GEO_IP_INSTALLED:
    try:
        GEO_IP_ENGINE = GeoIP.open(GEO_IP_CITY_DATABASE, GeoIP.GEOIP_STANDARD)
    except:
        GEO_IP_CITY_INSTALLED = False

"""
 print httpagentparser.detect(s)
{'dist': {'version': '2.3.5', 'name': 'Android'}, 'os': {'name': 'Linux'}, 'browser': {'version': '4.0', 'name': 'Safari'}}

History

http://stackoverflow.com/questions/927552/parsing-http-user-agent-string/1151956#1151956
"""

# Define a User
class Client(object):
    def __init__(self,user_name,user_agent, ip_address_id=None, dist_version=None, dist_name=None, os_name=None,
                 browser_version=None, browser_name=None):
        self.user_name = user_name
        self.user_agent = user_agent
        self.ip_address_id = ip_address_id
        self.dist_version = dist_version
        self.dist_name = dist_name
        self.os_name = os_name
        self.browser_version = browser_version
        self.browser_name = browser_name

    def __repr__(self):
        return "<%s,%s>" % (self.user_name, self.user_agent)

# Define Ip Address table
class IpAddress(object):
    def __init__(self,ip, country, domain_name=None,country_code3=None, country_name=None, city=None, region=None,
                 region_name=None, postal_code=None,latitude=None, longitude=None, area_code=None, time_zone=None,
                 metro_code=None):
        self.ip = ip
        self.country = country
        self.domain_name = domain_name
        self.country_code = country
        self.country_code3 = country_code3
        self.country_name = country_name
        self.city = city
        self.region = region
        self.region_name = region_name
        self.postal_code = postal_code
        self.latitude = latitude
        self.longitude = longitude
        self.area_code = area_code
        self.time_zone = time_zone
        self.metro_code = metro_code

    def __repr__(self):
        return "<%s,%s,%s>" % (self.ip,self.country,self.domain_name)

# The meat of the log
class Request(object):
    def __init__(self, client_id, url, request_date_time, http_response_code, size_bytes, response_time_secs, path_id=None,
                 referer=None, referer_path_id = None):
        self.client_id = client_id
        self.url = url
        self.request_date_time = request_date_time
        self.http_response_code = http_response_code
        self.size_bytes = size_bytes
        self.path_id = path_id
        self.response_time_secs = response_time_secs
        self.referer = referer
        self.referer_path_id = referer_path_id


# Holds recursive path
class Path(object):
    def __init__(self,name,parent_id = None):
        self.name = name
        self.parent_id = parent_id
    def __repr__(self):
        return "<%s,%s,%s>" % (self.id,self.parent_id,self.name)
    
    
class BuildWebLogDb(object):
    def __init__(self, web_log_config_hash, look_up_names=False):
        self.log_file_path = web_log_config_hash["log_file_path"]
        
        if web_log_config_hash.has_key("log_rotated_file_path"):
            self.log_rotated_file_path = web_log_config_hash["log_rotated_file_path"]
        else:
            self.log_rotated_file_path = None
        
        if web_log_config_hash.has_key("log_rotated_uid"):
            self.log_rotated_uid = web_log_config_hash["log_rotated_uid"]
        else:
            self.log_rotated_uid = None
            
        self.delimiter = web_log_config_hash["delimiter"]
        self.positions_in_log = web_log_config_hash["positions_in_log"]
        self.db_connection_string = web_log_config_hash["db_connection_string"]
        self.look_up_names = look_up_names
        self.metadata = MetaData()
        
        
        self.ip_address_table = Table('ip_address',self.metadata, Column('id',Integer,primary_key=True),
                             Column('ip',String(255),index=True,unique=True),
                             Column('domain_name',String(255)),
                             Column('country',String(255)),
                             Column('country_code3',String(255)),
                             Column('country_name',String(255)),
                             Column('city',String(255)),
                             Column('region',String(255)),
                             Column('region_name',String(255)),
                             Column('postal_code',String(255)),
                             Column('latitude',String(255)),
                             Column('longitude',String(255)),
                             Column('area_code',String(255)),
                             Column('time_zone',String(255)),
                             Column('metro_code',String(255))
                             )
        
        self.client_table = Table("client",self.metadata,Column('id',Integer,primary_key=True),
                                  Column('user_name',String(255)),
                                  Column('user_agent',String(4096)),
                                  Column('ip_address_id',Integer,ForeignKey('ip_address.id')),
                                  Column('dist_version',String(255)),
                                  Column('dist_name',String(255)),
                                  Column('os_name',String(255)),
                                  Column('browser_version',String(255)),
                                  Column('browser_name',String(255))
                                  )
        
        self.path_table = Table('path',self.metadata,Column('id',Integer,primary_key=True),
                           Column('parent_id',Integer,ForeignKey('path.id'),index=True),
                           Column('name',String(1024),index=True,unique=False))
        
        self.request_table = Table('request',self.metadata, Column('id',Integer,primary_key=True),
                              Column('client_id',Integer,ForeignKey('client.id'),index=True),
                              Column('path_id',Integer,ForeignKey('path.id'),index=True),
                              Column('referer_path_id',Integer,ForeignKey('path.id'),index=True),
                              Column('url',String(2048),index=True),
                              Column('referer',String(4096)),
                              Column('request_date_time',DateTime,index=True),
                              Column('response_time_secs',Integer),
                              Column('size_bytes',Integer),
                              Column('http_response_code',Integer))
        
        try:
            self.engine = create_engine(self.db_connection_string)
        except:
            raise
        
        self.build()
        self.Session = sessionmaker(bind = self.engine, autoflush=True)
        
        # define the ORM
        try:
            mapper(IpAddress,self.ip_address_table)
            mapper(Client,self.client_table)
            mapper(Request,self.request_table)
            mapper(Path, self.path_table, properties = {'children' : relation(Path,backref=backref('parent',remote_side=[self.path_table.c.id]))})
        except:
	    	pass
        
    def build(self):
        self.metadata.create_all(self.engine)

    def close(self):
        self.engine.dispose()

    def write_delimited(self,external_file_name_to_write,delimiter='\t'):
        """Writes a delimited version of the log file out"""
        positions_in_log = self.positions_in_log

        try:
            log_file = open(self.log_file_path)
        except IOError:
            print "Log file %s could not be opened" % os.path.abspath(self.log_file_path)
            sys.exit()

        try:
            external_file_to_write = open(external_file_name_to_write,'w')
            ef2w = external_file_to_write
        except IOError:
            print "Raw file %s could not be opened for writing" % os.path.abspath(external_file_name_to_write)
            
        ef2w.write("url" + delimiter)
        ef2w.write("path" + delimiter)
        ef2w.write("request_date_time" + delimiter)
        ef2w.write("ip" + delimiter)
        ef2w.write("http_response_code" + delimiter)
        ef2w.write("size_bytes" + delimiter)
        ef2w.write("response_time_secs" + delimiter)
        ef2w.write("referer" + delimiter)
        ef2w.write("user_agent" + delimiter)
        ef2w.write("user_name" + "\n")

        def strip_None(x):
            if x is None:
                return ''
            else:
                return x

        i = 0
        for line in log_file:
            i += 1
            request_data = split_request(line,self.delimiter)
            request_date_time = extract_date(request_data[positions_in_log["request_date_time"]])
            ip = evaluate_string("client_ip", positions_in_log, request_data)
            http_response_code = evaluate_string("http_response_code", positions_in_log, request_data)        
            size_bytes = evaluate_string("size_bytes", positions_in_log, request_data)
            response_time_secs = evaluate_string("request_response_time_secs", positions_in_log, request_data)
            referer = evaluate_string("referer", positions_in_log, request_data)
            domain_name = evaluate_string("domain_name", positions_in_log, request_data)
            user_agent = evaluate_string("user_agent", positions_in_log, request_data)
            user_name = evaluate_string("user_name", positions_in_log, request_data)
                            #handle the path
            url = evaluate_string("request_url", positions_in_log, request_data)
            (path,path_parts) = get_url_path_and_split(extract_url_from_request(url))

            ef2w.write("%s%s" % (strip_None(url),delimiter))
            ef2w.write("%s%s" % (strip_None(path),delimiter))
            ef2w.write("%s%s" % (strip_None(request_date_time),delimiter))
            ef2w.write("%s%s" % (strip_None(ip),delimiter))
            ef2w.write("%s%s" % (strip_None(http_response_code),delimiter))
            ef2w.write("%s%s" % (strip_None(size_bytes),delimiter))
            ef2w.write("%s%s" % (strip_None(response_time_secs),delimiter))
            ef2w.write("%s%s" % (strip_None(referer),delimiter))
            ef2w.write("%s%s" % (strip_None(user_agent),delimiter))
            ef2w.write("%s%s" % (strip_None(user_name),"\n"))

            if i % 10000 == 0:
                print("Writing line %s" % i)

    def is_log_rotated(self):
        log_has_rotated = False
        if self.log_rotated_file_path:
            file_uid = calculate_file_uid(self.log_rotated_file_path)
            
            if file_uid == self.log_rotated_uid:
                log_has_rotated = False
            else:
                log_has_rotated = True
            
        return log_has_rotated
    
    def update(self):
        """Public update web logs"""
        if self.is_log_rotated(): # handle rotated log file path
            current_file_path = self.log_file_path
            self.log_file_path = self.log_rotated_file_path
            self._update()
            self.log_file_path = current_file_path
        
        self._update()
    
    def _update(self):
        session = self.Session()
        positions_in_log = self.positions_in_log
        
        try:
            log_file = open(self.log_file_path)
        except IOError:
            print "Log file %s could not be opened" % os.path.abspath(self.log_file_path)
            sys.exit()
        
        max_date = self.max_date()
        
        max_date_record_set = session.query(Request).filter_by(request_date_time = max_date).all()
        max_date_record_set_n = len(max_date_record_set)
        
        if not self.count():
            root_path = Path('/',None) # this is the web root
            session.add(root_path)
        
        i = 0 # Keep count for committing sessions
        j = 1 # for syncing records when the log file rotates or changes
        new_request = False
        for line in log_file:
            i += 1
            request_data = split_request(line,self.delimiter)
            request_date_time = extract_date(request_data[positions_in_log["request_date_time"]])
            ip = evaluate_string("client_ip", positions_in_log, request_data)
            http_response_code = evaluate_string("http_response_code", positions_in_log, request_data)        
            size_bytes = evaluate_string("size_bytes", positions_in_log, request_data)
            response_time_secs = evaluate_string("request_response_time_secs", positions_in_log, request_data)
            referer = evaluate_string("referer", positions_in_log, request_data)
            domain_name = evaluate_string("domain_name", positions_in_log, request_data)
            user_agent = evaluate_string("user_agent", positions_in_log, request_data)
            user_name = evaluate_string("user_name", positions_in_log, request_data)
                
            if max_date is None:
                new_request = True
            elif request_date_time >= max_date:
                if  i == 1: # this is a new log file
                    max_date = None
                    new_request = True
                else: # log file has not been rotated but added to
                    if j >= max_date_record_set_n:
                        max_date = None
                    else:
                        j += 1
                
            if new_request:
            # Request time
                ip_address_obj = session.query(IpAddress).filter_by(ip = ip).first() # Build the client information
                if not ip_address_obj:
                    if self.look_up_names:
                        try:
                            domain_name = socket.gethostbyaddr(ip)[0]
                        except socket.herror:
                            domain_name = None
                    else:
                        domain_name = None

                    country_code = None
                    country_code3 = None
                    country_name = None
                    city  = None
                    region = None
                    region_name = None
                    postal_code = None
                    latitude = None
                    longitude = None
                    area_code = None
                    time_zone = None
                    metro_code = None

                    if GEO_IP_INSTALLED:
                        if GEO_IP_CITY_DATABASE:
                            gir = GEO_IP_ENGINE.record_by_addr(ip)
                            if gir:
                                country_code = gir['country_code']
                                country_code3 = gir['country_code3']
                                country_name = gir['country_name']
                                city = gir['city']
                                region = gir['region']
                                region_name =  gir['region_name']
                                postal_code = gir['postal_code']
                                latitude = gir['latitude']
                                longitude = gir['longitude']
                                area_code = gir['area_code']
                                time_zone = gir['time_zone']
                                metro_code = gir['metro_code']
                        else:
                            country_code = GeoByIp.country_code_by_addr(ip)
                    else:
                        country = None
                    
                    ip_address_obj = IpAddress(ip, country=country_code, domain_name=domain_name, country_code3=country_code3,
                                               country_name=country_name, city=city, region=region, region_name=region_name,
                                               postal_code=postal_code, latitude=latitude, longitude=longitude,
                                               area_code=area_code,time_zone=time_zone, metro_code=metro_code)
                    session.add(ip_address_obj)
                
                # we will not worry about user name just match by user agent
                client_obj = session.query(Client).filter_by(ip_address_id = ip_address_obj.id).filter_by(user_agent = user_agent).first()

                """
 print httpagentparser.detect(s)
{'dist': {'version': '2.3.5', 'name': 'Android'}, 'os': {'name': 'Linux'}, 'browser': {'version': '4.0', 'name': 'Safari'}}

History

http://stackoverflow.com/questions/927552/parsing-http-user-agent-string/1151956#1151956
"""

                if client_obj is None:
                    dist_version = None
                    dist_name = None
                    os_name = None
                    browser_version = None
                    browser_name = None

                    if user_agent:
                        user_agent_parsed = httpagentparser.detect(user_agent)

                        if "dist" in user_agent_parsed:
                            dist = user_agent_parsed["dist"]
                            if "version" in dist:
                                dist_version = dist["version"]
                            if "name" in dist:
                                dist_name = dist["name"]

                        if "os" in user_agent_parsed:
                            os_obj = user_agent_parsed["os"]
                            if "name" in os_obj:
                                os_name = os_obj["name"]

                        if "browser" in user_agent_parsed:
                            browser = user_agent_parsed["browser"]
                            if "version" in browser:
                                browser_version = browser["version"]
                            if "name" in browser:
                                browser_name = browser["name"]


                    client_obj = Client(user_name, user_agent, ip_address_obj.id, dist_name=dist_name,
                                        dist_version=dist_version, os_name=os_name, browser_name=browser_name, browser_version=browser_version)
                    session.add(client_obj)
                                    
                #handle the path
                url = evaluate_string("request_url", positions_in_log, request_data)
                (path,path_parts) = get_url_path_and_split(extract_url_from_request(url))
                
                if path_parts[-1] == "": # Remove an empty end path
                    path_parts = path_parts[:-1]
                
                not_known_paths = []
                path_obj = None
                while path_obj == None: # take path part until we find a parent path that matches
                    path_obj = session.query(Path).filter_by(name = path).first()
                    if not path_obj: # parent path does not match so continue
                        new_path_obj = Path(path)
                        not_known_paths.append(new_path_obj)
                        path_parts = path_parts[:-1]
                        path = join(path_parts,"/") + "/"
                         
                not_known_paths.reverse()
                for new_path_obj in not_known_paths: # Build the new path
                    new_path_obj.parent_id = path_obj.id
                    session.add(new_path_obj)
                    session.commit() # commit here because id was not being set
                    path_obj = new_path_obj
                
                request_obj = Request(client_obj.id, url, request_date_time, http_response_code, size_bytes,response_time_secs, path_obj.id, referer)
                session.add(request_obj)
        
                if (i % 1000) == 0:  #every 1000 records commit
                    session.commit()
                    print i
        session.commit() #Lets not lose the last record


    def count(self):
        session = self.Session()
        request_count = session.execute('select count(*) as count from request').fetchone()[0]
        session.close()
        return request_count


    def max_date(self):
        session = self.Session()
        max_date_time = session.execute('select max(request_date_time) as max_date_time from request').fetchone()[0]
        session.close()
        if max_date_time is not None:
            max_date_time = str(max_date_time).split(".")[0]
            max_date_time = datetime.datetime.strptime(max_date_time, "%Y-%m-%d %H:%M:%S")
        return max_date_time


def date_greater_than_or_equal(date1_string,date2_string):
    return date_to_long(date1_string) >= date_to_long(date2_string)


def date_to_long(date_string):
    long_date = int(date_string[0:4] + date_string[5:7] + date_string[8:10] + date_string[11:13] + date_string[14:16] + date_string[17:19])
    return long_date


def extract_date(date_time_string):
    date_time_data = date_time_string[1:-1].split()[0].split(':')
    hours = int(date_time_data[1])
    minutes = int(date_time_data[2])
    seconds = int(date_time_data[3])

    date_data = date_time_data[0].split('/')
    month_str = date_data[1]
    day = int(date_data[0])
    year = int(date_data[2])

    month_dict = {"Jan" : 1, "Feb" : 2, "Mar" : 3, "Apr" : 4, "May" : 5, "Jun" : 6, "Jul" :7, "Aug" : 8, "Sep" : 9, "Oct":10,"Nov":11,"Dec":12 }
    month = int(month_dict[month_str])
    
    return datetime.datetime(year,month,day,hours,minutes,seconds)


def get_url_path_and_split(url):
    file_path = urlparse(url)[2]
    file_path_split = file_path.split("/")
    return (file_path,file_path_split)


def join_escaped_space_split_string(split_string, start_escaped_character, end_escaped_character = None):
    
    if end_escaped_character is None:
        end_escaped_character = start_escaped_character
    
    string_buffer = ""  # hold joined string
    copied_split_string = [] # holds split array
    for i in range(len(split_string)):
        if (split_string[i][0] == start_escaped_character) and (split_string[i][-1] == end_escaped_character) and len(split_string[i]) > 1:
            copied_split_string.append(split_string[i])
        elif (split_string[i][0] == start_escaped_character) and (string_buffer == ""):
            string_buffer += split_string[i]
        elif (split_string[i][-1] == end_escaped_character) and (string_buffer != ""):
            string_buffer += (" " + split_string[i])
            copied_split_string.append(string_buffer)
            string_buffer = ""
        elif (string_buffer != ""):
            string_buffer += (" " + split_string[i])
        else:
            copied_split_string.append(split_string[i])
    
    return copied_split_string


def split_request(request_string, delimiter):
    request_string = request_string.rstrip()
    if delimiter == " ": 
        split_request_list = join_escaped_space_split_string(join_escaped_space_split_string(request_string.split(),'"'),"[","]")
    else:
        split_request_list = request_string.split(delimiter)
        
    return split_request_list


def evaluate_string(key_string, positions_in_log, request_data):
    if key_string in positions_in_log and positions_in_log[key_string] is not None:
        evaluated_value = request_data[positions_in_log[key_string]]
        if len(evaluated_value) > 0 and evaluated_value[0] == '"' and evaluated_value[-1] == '"': # handle strings which have been put in quotes
            evaluated_value = evaluated_value[1:-1]
        if evaluated_value == "-":
            evaluated_value = None
    else:
        evaluated_value = None
    return evaluated_value


def extract_url_from_request(client_request):
    if client_request:
        split_request = client_request.split()
        if len(split_request) == 1:
            return split_request[0]
        else:
            if len(split_request) == 0:
                return ""
            else:
                return split_request[1]
    else:
        return ""
